package com.foreveross.dao.imp;

import java.util.List;

import org.springframework.stereotype.Component;

import com.foreveross.common.Constants;
import com.foreveross.common.SQLUtil;
import com.foreveross.dao.OrderInfoDAO;
import com.foreveross.vo.OrderInfo;

@Component("orderInfoDAO")
public class OrderInfoDAOImp extends SQLUtil implements OrderInfoDAO {


	@Override
	public long findOrderInfoListCount(String weixinId,String userId,String houseId,String tenementId,String businessName,String hrFavorableId,
			String payType,String payStatus,String startOrderTime,String endOrderTime,String resCode) throws Exception {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select count (model.id) ");
		sqlStr.append(" from OrderInfo model ");
		sqlStr.append(" where 1=1 ");
		if(null!=weixinId && !"".equals(weixinId)){
			sqlStr.append(" and model.houseUser in (select b.id from HouseUser b where b.wechatId ='" + weixinId + "' and b.authenticationStatus = '"+Constants.AUTHENTICATIONSTATUS_SUCCESS+"' )");
		}
		if(null!=houseId && !"".equals(houseId)){
			sqlStr.append(" and model.hrHouses ='" + houseId + "'");
		}
		if(null!=tenementId && !"".equals(tenementId)){
			sqlStr.append(" and model.tenement ='" + tenementId + "'");
		}
		if(null!=businessName && !"".equals(businessName)){
			sqlStr.append(" and model.hrBusiness in (select c.id from HrBusiness c where c.name like '%"+businessName+"%')");
		}
		if(null!=hrFavorableId && !"".equals(hrFavorableId)){
			sqlStr.append(" and model.hrFavorable ='" + hrFavorableId + "'");
		}
		if(null!=payType && !"".equals(payType)){
			sqlStr.append(" and model.payType ='" + payType + "'");
		}
		if(null!=payStatus && !"".equals(payStatus)){
			sqlStr.append(" and model.payStatus ='" + payStatus + "'");
		}
		if(null!=startOrderTime && !"".equals(startOrderTime)){
			sqlStr.append(" and model.orderTime >='" + startOrderTime + "'");
		}
		if(null!=endOrderTime && !"".equals(endOrderTime)){
			sqlStr.append(" and model.orderTime <='" + endOrderTime + "'");
		}
		if(null!=resCode && !"".equals(resCode)){
			sqlStr.append(" and model.houseUser.resCode like '%" + resCode + "%'");
		}
		if(null!=userId && !"".equals(userId)){
			this.addAuthorityStr(sqlStr, userId,  "hrHouses");
		}

		sqlStr.append(" order by model.orderTime desc");
		return Long.parseLong(findObjectAll(sqlStr.toString()).get(0).toString()) ;
	}

	@Override
	public List<OrderInfo> findOrderInfoList(String weixinId,String userId,String houseId,String tenementId,String businessName,String hrFavorableId,
			String payType,String payStatus,String startOrderTime,String endOrderTime,String resCode,int pageIndex,
			int pageSize) throws Exception {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select distinct model ");
		sqlStr.append(" from OrderInfo model ");
		sqlStr.append(" where 1=1 ");
		if(null!=weixinId && !"".equals(weixinId)){
			sqlStr.append(" and model.houseUser in (select b.id from HouseUser b where b.wechatId ='" + weixinId + "' and b.authenticationStatus = '"+Constants.AUTHENTICATIONSTATUS_SUCCESS+"' )");
		}
		if(null!=houseId && !"".equals(houseId)){
			sqlStr.append(" and model.hrHouses ='" + houseId + "'");
		}
		if(null!=tenementId && !"".equals(tenementId)){
			sqlStr.append(" and model.tenement ='" + tenementId + "'");
		}
		if(null!=businessName && !"".equals(businessName)){
			sqlStr.append(" and model.hrBusiness in (select c.id from HrBusiness c where c.name like '%"+businessName+"%')");
		}

		if(null!=hrFavorableId && !"".equals(hrFavorableId)){
			sqlStr.append(" and model.hrFavorable ='" + hrFavorableId + "'");
		}
		if(null!=payType && !"".equals(payType)){
			sqlStr.append(" and model.payType ='" + payType + "'");
		}
		if(null!=payStatus && !"".equals(payStatus)){
			sqlStr.append(" and model.payStatus ='" + payStatus + "'");
		}
		if(null!=startOrderTime && !"".equals(startOrderTime)){
			sqlStr.append(" and model.orderTime >='" + startOrderTime + "'");
		}
		if(null!=endOrderTime && !"".equals(endOrderTime)){
			sqlStr.append(" and model.orderTime <='" + endOrderTime + "'");
		}
		if(null!=resCode && !"".equals(resCode)){
			sqlStr.append(" and model.houseUser.resCode like '%" + resCode + "%'");
		}
		if(null!=userId && !"".equals(userId)){
			this.addAuthorityStr(sqlStr, userId,  "hrHouses");
		}
		sqlStr.append(" order by model.orderTime desc");
		return (List<OrderInfo>) findObjectAll(sqlStr.toString(), pageIndex,
				pageSize);
	}


	public void deleteOrderInfo(String id) throws Exception{
		final String hql = "delete from OrderInfo t where t.id='" + id + "'";
		updateObejctByHql(hql);
	}

	@Override
	public long findOrderInfoListCountWithOutManager(String weixinId,
			String userId, String houseId, String tenementId,
			String businessName, String hrFavorableId, String payType,
			String payStatus, String startOrderTime, String endOrderTime,String resCode) throws Exception {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select count(*) ");
		sqlStr.append(" from order_info model ");
		sqlStr.append(" left join (select id from order_info where pay_type=1 and pay_status=0 ) b on model.id = b.id");
		sqlStr.append(" where 1=1 and b.id is null ");
		if(null!=weixinId && !"".equals(weixinId)){
			sqlStr.append(" and model.user_id in (select b.id from house_user b where b.wechat_id ='" + weixinId + "' and b.authentication_status = 1 )");
		}
		if(null!=houseId && !"".equals(houseId)){
			sqlStr.append(" and model.house_id ='" + houseId + "'");
		}
		if(null!=tenementId && !"".equals(tenementId)){
			sqlStr.append(" and model.tenement_id ='" + tenementId + "'");
		}
		if(null!=businessName && !"".equals(businessName)){
			sqlStr.append(" and model.business_id in (select c.id from hr_business c where c.name like '%"+businessName+"%')");
		}
		if(null!=hrFavorableId && !"".equals(hrFavorableId)){
			sqlStr.append(" and model.favorable_id ='" + hrFavorableId + "'");
		}
		if(null!=payType && !"".equals(payType)){
			sqlStr.append(" and model.pay_type ='" + payType + "'");
		}
		if(null!=payStatus && !"".equals(payStatus)){
			sqlStr.append(" and model.pay_status ='" + payStatus + "'");
		}
		if(null!=startOrderTime && !"".equals(startOrderTime)){
			sqlStr.append(" and model.order_time >='" + startOrderTime + "'");
		}
		if(null!=endOrderTime && !"".equals(endOrderTime)){
			sqlStr.append(" and model.order_time <='" + endOrderTime + "'");
		}
		if(null!=resCode && !"".equals(resCode)){
			sqlStr.append(" and model.user_id in (select b.id from house_user b where b.resCode ='%" + resCode + "%' and b.authentication_status = 1 )");
		}
		if(null!=userId && !"".equals(userId)){
			//+ " in (select rh.hrHouses.id from SysRoleHouse rh where rh.sysRole.id in (select sysRole.id from  SysUserRole ur where ur.sysUser.id='"
			sqlStr.append(" and model.house_id in (");
			sqlStr.append(" select house.id from hr_houses house,sys_role_house roleH,sys_role role,sys_user_role userR ");
			sqlStr.append(" where house.id = roleH.house_id and roleH.role_id = role.id and userR.sys_role_id = role.id and userR.sys_user_id='"+userId+"' ");
			sqlStr.append(")");
			//this.addAuthorityStr(sqlStr, userId,  "hrHouses");
		}

		sqlStr.append(" order by model.order_time desc");
		return Long.parseLong(findObejctBySQL(sqlStr.toString()).toString()) ;
	}

	@Override
	public List<OrderInfo> findOrderInfoListWithOutManager(String weixinId,
			String userId, String houseId, String tenementId,
			String businessName, String hrFavorableId, String payType,
			String payStatus, String startOrderTime, String endOrderTime,String resCode,
			int pageIndex, int pageSize) throws Exception {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append(" select model.* ");
		sqlStr.append(" from order_info model ");
		sqlStr.append(" left join (select id from order_info where pay_type=1 and pay_status=0 ) b on model.id = b.id");
		sqlStr.append(" where 1=1 and b.id is null ");
		if(null!=weixinId && !"".equals(weixinId)){
			sqlStr.append(" and model.user_id in (select b.id from house_user b where b.wechat_id ='" + weixinId + "' and b.authentication_status = 1 )");
		}
		if(null!=houseId && !"".equals(houseId)){
			sqlStr.append(" and model.house_id ='" + houseId + "'");
		}
		if(null!=tenementId && !"".equals(tenementId)){
			sqlStr.append(" and model.tenement_id ='" + tenementId + "'");
		}
		if(null!=businessName && !"".equals(businessName)){
			sqlStr.append(" and model.business_id in (select c.id from hr_business c where c.name like '%"+businessName+"%')");
		}
		if(null!=hrFavorableId && !"".equals(hrFavorableId)){
			sqlStr.append(" and model.favorable_id ='" + hrFavorableId + "'");
		}
		if(null!=payType && !"".equals(payType)){
			sqlStr.append(" and model.pay_type ='" + payType + "'");
		}
		if(null!=payStatus && !"".equals(payStatus)){
			sqlStr.append(" and model.pay_status ='" + payStatus + "'");
		}
		if(null!=startOrderTime && !"".equals(startOrderTime)){
			sqlStr.append(" and model.order_time >='" + startOrderTime + "'");
		}
		if(null!=endOrderTime && !"".equals(endOrderTime)){
			sqlStr.append(" and model.order_time <='" + endOrderTime + "'");
		}
		if(null!=resCode && !"".equals(resCode)){
			sqlStr.append(" and model.user_id in (select b.id from house_user b where b.resCode ='%" + resCode + "%' and b.authentication_status = 1 )");
		}
		if(null!=userId && !"".equals(userId)){
			//+ " in (select rh.hrHouses.id from SysRoleHouse rh where rh.sysRole.id in (select sysRole.id from  SysUserRole ur where ur.sysUser.id='"
			sqlStr.append(" and model.house_id in (");
			sqlStr.append(" select house.id from hr_houses house,sys_role_house roleH,sys_role role,sys_user_role userR ");
			sqlStr.append(" where house.id = roleH.house_id and roleH.role_id = role.id and userR.sys_role_id = role.id and userR.sys_user_id='"+userId+"' ");
			sqlStr.append(")");
			//this.addAuthorityStr(sqlStr, userId,  "hrHouses");
		}

		sqlStr.append(" order by model.order_time desc");
		return findObejctBySQL(sqlStr.toString(), OrderInfo.class,pageIndex,pageSize);
	}


}
